#coding: utf8
import datetime
import MySQLdb
from fresh.items import RestaurantItem
from fresh.items import RestaurantReview
from fresh.utils import Utils
from fresh.utils import GoogleAddressAttr
from fresh.utils import DatabaseDetails
from scrapy import log
import re
from pygeocoder import Geocoder
import time


class PaginasMasterDB(object):

    """
    host = "mysql1.alwaysdata.com"
    user = "62465_jmgost"
    passw = "sygnium"
    skema = "freshbuzzing_1"
    

    host = "localhost"
    user = "root"
    passw = "password"
    skema = "fresh"
    """


    utils = None

    logLocalName="PAGINAS_MAST_SCRAPPY"

    INSERT="INSERT"
    UPDATE="UPDATE"
    ACTIVE='Y'
    NACTIVE='N'    


    def getCursor(self):
        try:
            self.cursor = self.db.cursor()
            self.cursor.execute('SET NAMES utf8;')
            self.cursor.execute('SET CHARACTER SET utf8;')
            self.cursor.execute('SET character_set_connection=utf8;')
        except Exception,e:
            self.utils.logProcessDetail("Error al crear cursor MySQLdb:  " + str(e), self.utils.ERROR)
    

    def connectDB(self):
        try:
            props = DatabaseDetails()
            self.db = MySQLdb.connect(props.host,props.user,props.passw,props.skema)
            self.db.set_character_set('utf8')
        except Exception,e:
            self.utils.logProcessDetail("Error al conectar a base de datos MySQLdb.connect: " + str(e), self.utils.ERROR)
            return False
        return True


    def readURLs(self,urls):
        return
    def getRestFromUrl(self,url):
        return


    def insertRestaurants(self,restaurant,insupdate):
        try:
            sql=""
            db_id_rest=None
            db_lat=None
            db_long=None
            db_id_rest_mast=None
            db_rest_name=None
            db_bLockAddress=None
            db_bLockName=None
            db_address=None
            db_address_format=None
            db_address_google=None
            db_cp=None
            db_city=None
            db_location=None

            googleAttr = GoogleAddressAttr()

            if insupdate==self.INSERT:
                #sql = "SELECT ID_REST_SOURCE, NUM_REST_LATITUDE, NUM_REST_LONGITUDE,ID_RESTAURANT,DS_REST_NAME,BL_LOCK_ADDRESS, BL_LOCK_NAME,DS_REST_ADDRESS,DS_REST_ADDRESS_FORMATTED, DS_REST_ADDRESS_GOOGLE,DS_REST_ZIP_CODE, ID_REST_CITY,  AUX_DS_GOOGLE_LOCATION FROM MAST_REST_PAGINASAMARILLAS WHERE DS_REST_NAME=%s and  DS_REST_ADDRESS=%s"
                sql = "SELECT ID_REST_SOURCE, NUM_REST_LATITUDE, NUM_REST_LONGITUDE,ID_RESTAURANT,DS_REST_NAME,BL_LOCK_ADDRESS, BL_LOCK_NAME,DS_REST_ADDRESS,DS_REST_ADDRESS_FORMATTED, DS_REST_ADDRESS_GOOGLE,ID_COUNTRY_3, ID_ADMIN_AREA_L1, ID_ADMIN_AREA_L2, ID_LOCALITY, ID_ZIP_CODE,  AUX_DS_GOOGLE_LOCATION FROM MAST_REST_PAGINASAMARILLAS WHERE DS_REST_NAME=%s and  DS_REST_ADDRESS=%s"
                self.getCursor()
                self.cursor.execute(sql,(self.utils.convertListToString(restaurant['restaurant_name']),self.utils.convertListToString(restaurant['address'])))
            elif insupdate==self.UPDATE:
                """when just update the ID_REST_SOURCE in restaurant is filled in"""
                sql = "SELECT ID_REST_SOURCE, NUM_REST_LATITUDE, NUM_REST_LONGITUDE,ID_RESTAURANT,DS_REST_NAME,BL_LOCK_ADDRESS, BL_LOCK_NAME,DS_REST_ADDRESS,DS_REST_ADDRESS_FORMATTED, DS_REST_ADDRESS_GOOGLE,DS_REST_ZIP_CODE, ID_REST_CITY,  AUX_DS_GOOGLE_LOCATION FROM MAST_REST_PAGINASAMARILLAS WHERE ID_REST_SOURCE=%s"
                self.getCursor()
                self.cursor.execute(sql,(restaurant['idRestSource']))
            else:
                self.utils.logProcessDetail("Error de programación!!, insertRestaurants llamado con parametro insupdate no valido", self.utils.ERROR)
                return

            
            data=self.cursor.fetchone();
            if data is not None:
                """insert turns to update"""
                if insupdate==self.INSERT:
                    insupdate=self.UPDATE
                    
                self.utils.log("DEBUG", "data: " + str(data),self.utils.DEBUG)
                db_id_rest=data[0]
                db_lat=data[1]
                db_long=data[2]
                db_id_rest_mast=data[3]
                db_rest_name=data[4]
                db_bLockAddress=data[5]
                db_bLockName=data[6]
                db_address=data[7]
                db_address_format=data[8]
                db_address_google=data[9]
                
                db_country=data[10]
                db_AAL1=data[11]
                db_AAL2=data[12]
                db_locality=data[13]
                db_cp=data[14]
                db_location=data[15]
                
                """before any action let's check there's data from the scrappy, if not let's deactivate the restaurant"""
                if len(self.utils.cleanString(self.utils.convertListToString(restaurant['restaurant_name'])))==0:
                    self.setRestaurantStatus(db_id_rest,self.NACTIVE)
                    return
                else:
                    self.setRestaurantStatus(db_id_rest,self.ACTIVE)
                
                if db_bLockAddress==self.utils.IND_DB_YES:
                    if db_bLockName==self.utils.IND_DB_YES:
                        """ALL BLOCKED--> copy DB data into record, so no overwritting takes place"""
                        googleAttr.address_format=db_address_format
                        googleAttr.lat=db_lat
                        googleAttr.long=db_long
                        googleAttr.location=db_location

                        googleAttr.country_3=db_country
                        googleAttr.admin_area_L1=db_AAL1
                        googleAttr.admin_area_L2=db_AAL2
                        googleAttr.locality=db_locality
                        googleAttr.zip=db_cp
                        
                        restaurant['restaurant_name']=db_rest_name
                        restaurant['address_google']=db_address_google
                        restaurant['address']=db_address
                    else:
                        """ADDRESS BLOCKED-->copy DB data into record, so no overwritting takes place"""
                        googleAttr.address_format=db_address_format
                        googleAttr.lat=db_lat
                        googleAttr.long=db_long
                        googleAttr.location=db_location

                        googleAttr.country_3=db_country
                        googleAttr.admin_area_L1=db_AAL1
                        googleAttr.admin_area_L2=db_AAL2
                        googleAttr.locality=db_locality
                        googleAttr.zip=db_cp
                        
                        """restaurant['restaurant_name']=db_rest_name"""
                        restaurant['address_google']=db_address_google
                        restaurant['address']=db_address
                        
                else:
                    """ADDRESS NOT BLOCKED --> let's get google info if necessary"""
                    """before fill-in data to avoid additional if-else"""
                    googleAttr.address_format=db_address_format
                    googleAttr.lat=db_lat
                    googleAttr.long=db_long
                    googleAttr.location=db_location


                    googleAttr.country_3=db_country
                    googleAttr.admin_area_L1=db_AAL1
                    googleAttr.admin_area_L2=db_AAL2
                    googleAttr.locality=db_locality
                    googleAttr.zip=db_cp
                    
                    if db_lat is not None and db_long is not None:
                        if db_lat == 0 or db_long == 0:
                            self.utils.getGoogleCoordinates(self.utils.convertListToString(restaurant['address_google']).encode( "utf-8" ),"",googleAttr)
                        else:
                            """in case address changed it is necessary to calculate coordenates again too"""
                            if db_address!=self.utils.convertListToString(restaurant['address']):
                                self.utils.getGoogleCoordinates(self.utils.convertListToString(restaurant['address_google']).encode( "utf-8" ),"",googleAttr)
                    else:
                        """coordinates not defiend, let's get them"""
                        self.utils.getGoogleCoordinates(self.utils.convertListToString(restaurant['address_google']).encode( "utf-8" ),"",googleAttr)
                        self.utils.log("DEBUG","coordinates: "+ str(googleAttr),self.utils.DEBUG)
                        
                    if db_bLockName==self.utils.IND_DB_YES:
                        """ADDRESS NOT BLOCKED, NAME BLOCKED--> copy db name into record"""
                        restaurant['restaurant_name']=db_rest_name                                
            else:
                if insupdate==self.UPDATE:
                    self.utils.logProcessDetail("SE SUPONIA QUE TENIAMOS QUE HACER UPDATE DE RESTAURANTE PERO NO SE ENCUENTRA EL RESTAURANTE CON idRestSource: " + str(restaurant), self.utils.ERROR)
                    return
                else:
                    self.utils.getGoogleCoordinates(self.utils.convertListToString(restaurant['address_google']).encode( "utf-8" ),"",googleAttr)
                    

            """-----------------------------------------------------------------"""
            """ All data properly set up taking into consideration blocked data"""
            """-----------------------------------------------------------------"""
            if insupdate==self.INSERT:            
                self.utils.log("DEBUG", "VAMOS A INSERTAR" + self.utils.convertListToString(restaurant['restaurant_name']),self.utils.DEBUG)
                #sql = "INSERT INTO MAST_REST_PAGINASAMARILLAS (DS_REST_ADDRESS_FORMATTED, DS_REST_NAME, DS_REST_ADDRESS_GOOGLE, AUX_DS_GOOGLE_LOCATION,DS_REST_ADDRESS, DS_REST_ZIP_CODE, ID_REST_CITY, NUM_REST_LATITUDE, NUM_REST_LONGITUDE, DS_URL_PAGINAS, DS_URL_REST, NUM_TELEPHONE, DS_EMAIL,NUM_SCORE, NUM_POSTS, DT_LAST_UPDATED) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,NOW())"
                sql = "INSERT INTO MAST_REST_PAGINASAMARILLAS (DS_REST_ADDRESS_FORMATTED, DS_REST_NAME, DS_REST_ADDRESS_GOOGLE, AUX_DS_GOOGLE_LOCATION,DS_REST_ADDRESS, ID_COUNTRY_3, ID_ADMIN_AREA_L1, ID_ADMIN_AREA_L2, ID_LOCALITY,ID_ZIP_CODE, NUM_REST_LATITUDE, NUM_REST_LONGITUDE, DS_URL_PAGINAS, DS_URL_REST, NUM_TELEPHONE, DS_EMAIL,NUM_SCORE, NUM_POSTS, DT_LAST_UPDATED) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,NOW())" 
                self.cursor.execute(sql,(googleAttr.address_format,self.utils.convertListToString(restaurant['restaurant_name']),self.utils.convertListToString(restaurant['address_google']),googleAttr.location,self.utils.convertListToString(restaurant['address']),googleAttr.country_3,googleAttr.admin_area_L1,googleAttr.admin_area_L2,googleAttr.locality,googleAttr.zip,googleAttr.lat,googleAttr.long,self.utils.convertListToString(restaurant['url_scrapy']),self.utils.convertListToString(restaurant['url_rest']),self.utils.convertListToString(restaurant['phone']),self.utils.convertListToString(restaurant['email']),self.utils.convertListToString(restaurant['score']),self.utils.convertListToString(restaurant['reviews_total'])))
                self.db.commit()

                self.cursor.execute('SELECT LAST_INSERT_ID()')
                data=self.cursor.fetchone();
                db_id_rest=data[0]         
            elif insupdate==self.UPDATE:
                self.utils.log("DEBUG","VAMOS A UPDATE ALL" + self.utils.convertListToString(restaurant['restaurant_name']),self.utils.DEBUG)
                sql = "UPDATE MAST_REST_PAGINASAMARILLAS SET DS_REST_ADDRESS_FORMATTED=%s,DS_REST_ADDRESS=%s,DS_REST_NAME=%s,DS_REST_ADDRESS_GOOGLE=%s, AUX_DS_GOOGLE_LOCATION=%s, DS_REST_ZIP_CODE=%s, ID_REST_CITY=%s ,NUM_REST_LATITUDE=%s, NUM_REST_LONGITUDE=%s, DS_URL_PAGINAS=%s,DS_URL_REST=%s,  NUM_TELEPHONE=%s, DS_EMAIL=%s, NUM_SCORE=%s, NUM_POSTS=%s, DT_LAST_UPDATED=NOW() WHERE ID_REST_SOURCE=%s"
                self.cursor.execute(sql,(googleAttr.address_format,self.utils.convertListToString(restaurant['address']),self.utils.convertListToString(restaurant['restaurant_name']),self.utils.convertListToString(restaurant['address_google']),googleAttr.location,self.utils.convertListToString(restaurant['cp']),self.utils.convertListToString(restaurant['city']),googleAttr.lat,googleAttr.long,self.utils.convertListToString(restaurant['url_scrapy']),self.utils.convertListToString(restaurant['url_rest']),self.utils.convertListToString(restaurant['phone']),self.utils.convertListToString(restaurant['email']),self.utils.convertListToString(restaurant['score']),self.utils.convertListToString(restaurant['reviews_total']),str(db_id_rest)))
                self.db.commit()                    

            else:
                self.utils.logProcessDetail("Error de programación en __insertUpdateRestaurant variable insupdate con valor no posible: " +str(insupdate), self.utils.ERROR)
                return
            """------------- end insert/update restaurant---------------------------"""


                
        except Exception,e:
            self.utils.logProcessDetail("Error al ejecutar sentencia SQL1: " + str(sql) + "RESTAURANT: " + str(restaurant) + "Exception: " + str(e), self.utils.ERROR)
            self.db.rollback()
        else:
            self.db.commit()
        finally:
            self.cursor.close()


    def setRestaurantStatus(self,id_rest,status):

        if status==self.NACTIVE:
            self.utils.logProcessDetail("Desactivar Restaurante id_source: "+str(id_rest), self.utils.INFO)
            
        sql ="UPDATE  mast_rest_paginasamarillas SET BL_ACTIVE=%s, DT_LAST_UPDATED=NOW() WHERE ID_REST_SOURCE=%s"
        try:
            self.cursor.execute(sql,( str(status),str(id_rest)))
        except Exception,e:
            """probably tried to use internal cursor which is not valid because function called from outside, design problem, cursor should be factory!!!"""
            try:
                self.getCursor()
                self.cursor.execute(sql,( str(status),str(id_rest)))
            except Exception,e:
                self.db.rollback()
                self.utils.logProcessDetail("Error al setRestaurantNoActive id: " +str(id_rest) + "Error: "+ str(e),self.utils.ERROR)
            else:
                self.db.commit()
            finally:
                self.cursor.close()
        else:
            self.db.commit()




    def __del__(self):
        self.db.close()
        super(PaginasMasterDB, self).__del__()
    